Oracle 12cR2 表分区在线 DDL 功能带来的影响是什么？

原题：Oracle 12cR2 新特性研究：表分区在线DDL带来的影响

来自社区“平台人生”专栏：

http://www.talkwithtrend.com/Column/detail/id/11

背景

Oracle 12cR2提供了表（分区）在线DDL的功能，在执行MOVE、SPLIT操作时可以不影响对相关表（分区）的DML执行。

单独执行一次online的操作当然没问题，Oracle这么大的公司，这样基础的测试肯定是做了的。还需要关注的是如果连续多次执行类似操作，是否会出现内存泄漏等问题，毕竟一次正确执行并不难，难的是持续正确执行。

现象

我们定义了一个分区表，表上有一个全局索引和一个本地索引，在表上并发执行两个session，一个持续执行MOVE操作，一个持续执行insert操作，都在后台执行（具体脚本见附件1）。经过一晚上运行后，查看对比系统运行状态：

Ø SGA。比对运行前后SGA各主要组件是否有剧烈变化，如果有组件暴涨意味着可能会导致ORA-4031内存无法分配的问题。实际比对无异常。

Ø PGA。比对运行前后OS剩余内存和数据库PGA、Session PGA的使用是否有大的变化，如果有意味着可能会有PGA内存泄漏。实际比对无异常。

Ø 空间使用。MOVE过程中占用两份空间是正常的，INSERT也会造成空间使用的不断增加，但是空间使用的异常增加无疑是有问题的。对，你猜对了，空间使用确实异常增加了。

探究

全局索引占用空间达到了10个G，对比本地索引占用200M，分区空间共使用400M。rebuild重建后，全局索引空间使用200M。

进一步的测试发现，只要分区里面有数据，不需要INSERT，只MOVE就可以重现全局索引空间暴涨的现象。

深入研究得知，这是因为12c中对全局索引启用了异步维护机制（Asynchronous Global Index Maintenance）,文档（https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107）中仅提到了DROP、TRUNCATE操作，但MOVE也会造成这个效果。当数据库进行这些操作时，应该是仅仅将全局索引中失效的记录置了标志位，并没有立即处理，因此它们在索引中占据的空间无法被复用。
那何时会被处理呢？Oracle专门有一个自动任务SYS.PMO_DEFERRED_GIND_MAINT_JOB来处理所有的全局索引，该JOB设计为默认在每天凌晨2:00运行。手工可以直接调用DBMS_SCHEDULER.RUN_JOB的方式来启动。

扩展

本着一件事情给N种办法处理的原则，Oracle也提供了各种其他的办法，包括但不局限于：

Ø ALTER INDEX [PARTITION] COALESCE CLEANUP。COALESCE这个办法在对付enq：Index – contention的时候我们在业务低谷期用过，对DML基本没有影响，加个cleanup应该也影响不大。

Ø ALTER INDEX [PARTITION] REBULID。索引重建，最彻底的办法。

Ø DBMS_PART.CLEANUP_GIDX。可以对系统中所有需要清理的全局索引进行清理，也可以指定用户、指定表进行。它会调用前面的COALESCE CLEAN。

Ø DBMS_PART.CLEANUP_ONLINE_OP。

这个存储过程是专治在线MOVE中出现的各种问题，负责扫尾的，顺便也会把全局索引清理一下。

这些办法我们经过测试，把遇到的各种情况给大家一并汇报一下：

Ø DBMS_PART.CLEANUP_GIDX相当贴心，如果它没有发现任何需要清理的全局索引，也会抛出ORA-20000错误来送你个大大的礼包，惊喜不惊喜，意外不意外？

Ø 当执行完了DBMS_PART.CLEANUP_GIDX、COALESCE CLEANUP，多余的空间会释放吗？不会，仍然会维持在原来的大小。通过DBMS_SPACE.SPACE_USAGE（使用办法见附件2）对比执行前后该索引的块空间使用状况可以看到，处理前对象中基本都是Full Blocks，而处理后对象中的Unformatted Blocks和FS2 Blocks(25-50% Free)都有显著增加。此时这些空间才可能被重复利用。

Ø ALL_INDEXES里面有字段ORPHANED_ENTRIES可以展示该索引是否有stale entries，但该字段为字符型（存储值YES、NO），不是数字型。

Ø PMO_DEFERRED_GIND_MAINT_JOB在DBMS_SCHEDULER_JOBS视图中看LAST_START_DATE，确实在每日凌晨2点启动了，查看对象块空间使用情况也改变了，但在DBMS_SCHEDULER_JOB_RUN_DETAILS里面并没有对应记录，因此无法了解是否出现了任务超时等异常。

结论

1.DROP、TRUNCATE、MOVE等分区操作会导致全局索引空间占用增加，这是因为全局索引中有stale entries，这部分空间无法立即被复用。

2.Oracle提供了异步清理机制，根据当前理解Oracle提供信息不够详细，大数据量数据库中无法确认当天JOB是否处理正常完成了。

3.对大表的以上操作，还是建议手工处理，避免后续影响。

相关脚本

1、测试脚本

以下未指明均为非sys用户执行，sys用户不能执行自身表在线move操作，事先创建两个测试用表空间users、dbs2：

在一个session执行：

在另一个session执行：

2、查看对象空间使用脚本

更多相关主题文章，请点击阅读原文

长按二维码关注公众号